﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess_Layer
{
    public static class Order_DAC
    {
        public static OrderDTO get(long id)
        {
            List<SqlParameter> sqlParams = makeGetOrderParams(ref id);
            OrderDTO order = null;
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;

            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_Order", sqlParams);

                if (rdr.HasRows)
                {
                    if (rdr.Read())
                    {
                        order = createOrder(rdr);
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return order;
        }
        private static List<SqlParameter> makeGetOrderParams(ref long id)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            SqlParameter inParam = new SqlParameter("@id", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = id;
            sqlParams.Add(inParam);
            return sqlParams;
        }
        private static OrderDTO createOrder(SqlDataReader rdr)
        {
            OrderDTO order = new OrderDTO();
            order.Id = (long)rdr["ID"];
            order.CustomerID = (long)rdr["CustomerID"];
            order.CreatedDate = (DateTime)rdr["CreatedDate"];
            order.OrderStateID = (int)rdr["OrderStateID"];
            order.RetailCenterID = (long)rdr["RetailCenterID"]; ;
            return order;
        }

        public static int delivery(long orderID, long centerID)
        {
            List<SqlParameter> sqlParams = makeDeliveryParams(ref orderID, centerID);
            int result;
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;

            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Delivery", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
            result = (int)sqlParams[sqlParams.Count - 1].Value;

            return result;
        }
        private static List<SqlParameter> makeDeliveryParams(ref long orderID, long centerID)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@centerID", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = centerID;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@orderID", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = orderID;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static DataTable getEbookOf(string username)
        {
            DataTable dtab = null;

            List<SqlParameter> sqlParams = makeGetEbookParams(username);
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;

            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("GetAllEbookOf", sqlParams);

                int stt = 1;
                if (rdr.HasRows)
                {
                    dtab = new DataTable();
                    dtab.Columns.Add("STT");
                    dtab.Columns.Add("TenEbook");
                    dtab.Columns.Add("TenTacGia");
                    dtab.Columns.Add("DuongDan");
                }

                while (rdr.Read())
                {
                    //TODO: Thong bao doi voi cac ebook chua thanh toan thanh cong.
                    string ebookID = rdr["EbookID"].ToString();
                    dtab.Rows.Add(new object[] { stt, rdr["TenEbook"], rdr["TenTacGia"], "Download.aspx?ProdID=" + ebookID });
                    stt++;
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return dtab;
        }
        private static List<SqlParameter> makeGetEbookParams(string username)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            SqlParameter inParam = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = username;
            sqlParams.Add(inParam);
            return sqlParams;
        }

        public static bool checkEbookOwnBy(string productID, string username)
        {
            List<SqlParameter> sqlParams = makeGetEbookParams(username);
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;

            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("GetAllEbookOf", sqlParams);

                while (rdr.Read())
                {
                    if (rdr["EbookID"].ToString().Equals(productID))
                    {
                        return true;
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return false;
        }

        public static object tim(string tenDN, string diaChi, DateTime? tuNgay, DateTime? denNgay)
        {
            //4 cai null
            if (tenDN != null && diaChi != null && tuNgay != null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0 && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }

            //3 cai null
            if (tenDN == null && diaChi != null && tuNgay != null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0 && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN != null && diaChi == null && tuNgay != null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0 && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN != null && diaChi != null && tuNgay == null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN != null && diaChi != null && tuNgay != null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }

            //2 cai null
            if (tenDN == null && diaChi == null && tuNgay != null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.CreatedDate.Value.CompareTo(tuNgay) > 0 && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN == null && diaChi != null && tuNgay == null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN == null && diaChi != null && tuNgay != null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.DiaChiGH.Contains(diaChi) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }

            if (tenDN != null && diaChi == null && tuNgay == null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN != null && diaChi == null && tuNgay != null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.CreatedDate.Value.CompareTo(tuNgay) > 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN != null && diaChi != null && tuNgay == null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN) && pp.DiaChiGH.Contains(diaChi)).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }

            //1 cai null
            if (tenDN != null && diaChi == null && tuNgay == null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.Customer.UserName.Contains(tenDN)).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN == null && diaChi != null && tuNgay == null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.DiaChiGH.Contains(diaChi)).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN == null && diaChi == null && tuNgay != null && denNgay == null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.CreatedDate.Value.CompareTo(tuNgay) > 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }
            if (tenDN == null && diaChi == null && tuNgay == null && denNgay != null)
            {
                return DataAccessHelper.DB.Orders.Where(pp => pp.CreatedDate.Value.CompareTo(denNgay) < 0).Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
            }

            return DataAccessHelper.DB.Orders.Select(pp => new { pp.ID, TenDNKH = pp.Customer.UserName, pp.CustomerID, TenTT = pp.OrderState.Name, pp.OrderStateID, pp.CreatedDate, pp.NgayHangDuocGiao, pp.DaXoa, TenNVC = pp.NhaVanChuyen.Ten, pp.MaNhaVanChuyen, pp.DiaChiGH, pp.TongTien });
        }

        public static void delete(List<long> deletedIDs)
        {
            foreach (long deletedID in deletedIDs)
            {
                Order nccgh = DataAccessHelper.DB.Orders.Where(ncc => ncc.ID == deletedID).Select(ncc => ncc).First();
                DataAccessHelper.DB.Orders.DeleteOnSubmit(nccgh);
            }

            DataAccessHelper.Save();
        }

        public static void update(Order nCC_GiangHang)
        {
            Order nccgh = DataAccessHelper.DB.Orders.Where(ncc => ncc.ID == nCC_GiangHang.ID).Select(ncc => ncc).First();
            nccgh.CreatedDate = nCC_GiangHang.CreatedDate;
            nccgh.CustomerID = nCC_GiangHang.CustomerID;
            nccgh.CreatedDate = nCC_GiangHang.CreatedDate;
            nccgh.MaNhaVanChuyen = nCC_GiangHang.MaNhaVanChuyen;
            nccgh.TongTien = nCC_GiangHang.TongTien;
            nccgh.OrderStateID = nCC_GiangHang.OrderStateID;
            nccgh.NgayDangKyGH = nCC_GiangHang.NgayDangKyGH;
            nccgh.DaXoa = nCC_GiangHang.DaXoa;

            DataAccessHelper.Save();
        }
    }
}
